import pdfplumber
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
import re

def extract_table_from_pdf(pdf_path, excel_path):
    # 创建一个新的Excel工作簿
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    
    with pdfplumber.open(pdf_path) as pdf:
        # 遍历每一页
        for page in pdf.pages:
            # 提取当前页中的所有表格
            tables = page.extract_tables()
            
            for table in tables:
                # 遍历表格的每一行
                for row in table:
                    # 将每一行的数据写入Excel中的新行中
                    new_row = []
                    for cell in row:
                        # 检查单元格类型是否为字符串
                        if isinstance(cell, str):
                            # 删除换行符、制表符和空格
                            cell_text = re.sub(r'[\n\t\s]', '', cell)
                            new_row.append(cell_text)
                        else:
                            new_row.append(cell)
                    sheet.append(new_row)
    
    # 调整列宽以适应内容
    for column in sheet.columns:
        max_length = 0
        column_letter = get_column_letter(column[0].column)
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except TypeError:
                pass
        adjusted_width = (max_length + 2) * 1.2
        sheet.column_dimensions[column_letter].width = adjusted_width
                    
    # 保存Excel文件
    workbook.save(excel_path)
    print("表格已成功写入Excel文件！")

# 调用函数进行PDF表格提取并删除换行符、制表符和空格
pdf_file = "./202307.pdf"
excel_file = "./file.xlsx"
extract_table_from_pdf(pdf_file, excel_file)
